package simulaco.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import simulaco.dto.Questao;
import simulaco.dto.QuestaoComplemento;
import simulaco.dto.QuestaoComplementoTipo;
import simulaco.dto.Situacao;

public class QuestaoDAO extends DataAccessObject {

    public static Questao findByPK(Questao questao, Connection conn) throws TransactionException {

        return findByPK(questao.getConcurso().getCodigo(), questao.getMateria().getCodigo(), questao.getCodigo(), conn);
    }

    public static Questao findByPK(int concursoId, int materiaId, int questaoId, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO     CD_CONCURSO          ");
        sbSql.append("     , A.CD_MATERIA      CD_MATERIA           ");
        sbSql.append("     , A.CD_QUESTAO      CD_QUESTAO           ");
        sbSql.append("     , A.CD_COMPLEMENTO  CD_COMPLEMENTO       ");
        sbSql.append("     , C.ID_TIPO         ID_TIPO_COMPLEMENTO  ");
        sbSql.append("     , C.TX_TEXTO        TX_TEXTO_COMPLEMENTO ");
        sbSql.append("     , A.TX_QUESTAO      TX_QUESTAO           ");
        sbSql.append("     , A.TX_COMENTARIO   TX_COMENTARIO        ");
        sbSql.append("     , A.ID_SITUACAO     ID_SITUACAO          ");
        sbSql.append("  FROM QUESTAO                   A ");
        sbSql.append("  LEFT JOIN QUESTAO_COMPLEMENTO  C ");
        sbSql.append("    ON A.CD_CONCURSO    = C.CD_CONCURSO    ");
        sbSql.append("   AND A.CD_MATERIA     = C.CD_MATERIA     ");
        sbSql.append("   AND A.CD_COMPLEMENTO = C.CD_COMPLEMENTO ");
        sbSql.append(" WHERE A.CD_CONCURSO = ? ");
        sbSql.append("   AND A.CD_MATERIA  = ? ");
        sbSql.append("   AND A.CD_QUESTAO  = ? ");

        Questao questao = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoId);
            setInt(ps, 2, materiaId);
            setInt(ps, 3, questaoId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                questao = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return questao;
    }

    public static List<Questao> findByConcurso(int concursoId, Connection conn) throws TransactionException {

        return findByConcursoMateria(concursoId, 0, conn);
    }

    public static List<Questao> findByMateria(int materiaId, Connection conn) throws TransactionException {

        return findByConcursoMateria(0, materiaId, conn);
    }

    public static List<Questao> findByConcursoMateria(int concursoId, int materiaId, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO     CD_CONCURSO          ");
        sbSql.append("     , A.CD_MATERIA      CD_MATERIA           ");
        sbSql.append("     , A.CD_QUESTAO      CD_QUESTAO           ");
        sbSql.append("     , A.CD_COMPLEMENTO  CD_COMPLEMENTO       ");
        sbSql.append("     , ''                ID_TIPO_COMPLEMENTO  ");
        sbSql.append("     , ''                TX_TEXTO_COMPLEMENTO ");
        sbSql.append("     , A.TX_QUESTAO      TX_QUESTAO           ");
        sbSql.append("     , A.TX_COMENTARIO   TX_COMENTARIO        ");
        sbSql.append("     , A.ID_SITUACAO     ID_SITUACAO          ");
        sbSql.append("  FROM QUESTAO              A ");
        sbSql.append(" WHERE 1 = 1 ");
        if (concursoId > 0) {
            sbSql.append("   AND A.CD_CONCURSO = ? ");
        }
        if (materiaId > 0) {
            sbSql.append("   AND A.CD_MATERIA  = ? ");
        }

        List<Questao> questaosList = new ArrayList<Questao>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            if (concursoId > 0) {
                setInt(ps, pos++, concursoId);
            }
            if (materiaId > 0) {
                setInt(ps, pos++, materiaId);
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Questao questao = fromResultSet(rs);
                questaosList.add(questao);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoDAO.findByConcursoMateria (" + sqle.getMessage() + ")");
        }
        return questaosList;
    }

    public static List<Questao> findByComplemento(int complmentoId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO     CD_CONCURSO          ");
        sbSql.append("     , A.CD_MATERIA      CD_MATERIA           ");
        sbSql.append("     , A.CD_QUESTAO      CD_QUESTAO           ");
        sbSql.append("     , A.CD_COMPLEMENTO  CD_COMPLEMENTO       ");
        sbSql.append("     , ''                ID_TIPO_COMPLEMENTO  ");
        sbSql.append("     , ''                TX_TEXTO_COMPLEMENTO ");
        sbSql.append("     , A.TX_QUESTAO      TX_QUESTAO           ");
        sbSql.append("     , A.TX_COMENTARIO   TX_COMENTARIO        ");
        sbSql.append("     , A.ID_SITUACAO     ID_SITUACAO          ");
        sbSql.append("  FROM QUESTAO              A ");
        sbSql.append(" WHERE A.CD_COMPLEMENTO = ? ");

        List<Questao> questaosList = new ArrayList<Questao>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, complmentoId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Questao questao = fromResultSet(rs);
                questaosList.add(questao);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoDAO.findByComplemento (" + sqle.getMessage() + ")");
        }
        return questaosList;
    }

    public static void insert(Questao questao, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO QUESTAO     ");
        sbSql.append("     ( CD_CONCURSO      ");
        sbSql.append("     , CD_MATERIA       ");
        sbSql.append("     , CD_QUESTAO       ");
        sbSql.append("     , CD_COMPLEMENTO   ");
        sbSql.append("     , TX_QUESTAO       ");
        sbSql.append("     , TX_COMENTARIO    ");
        sbSql.append("     , ID_SITUACAO      ");
        sbSql.append(") VALUES (?,?,?,?,?,?,?)");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, questao.getConcurso().getCodigo());
            setInt(ps, pos++, questao.getMateria().getCodigo());
            setInt(ps, pos++, questao.getCodigo());
            setInt(ps, pos++, questao.getComplemento().getCodigo(), true);
            setString(ps, pos++, questao.getTexto());
            setString(ps, pos++, questao.getComentario(), true);
            setInt(ps, pos++, questao.getSituacao().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(Questao questao, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE QUESTAO            ");
        sbSql.append("   SET TX_QUESTAO     = ? ");
        sbSql.append("     , TX_COMENTARIO  = ? ");
        sbSql.append("     , ID_SITUACAO    = ? ");
        sbSql.append("     , CD_COMPLEMENTO = ? ");
        sbSql.append(" WHERE CD_CONCURSO    = ? ");
        sbSql.append("   AND CD_MATERIA     = ? ");
        sbSql.append("   AND CD_QUESTAO     = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setString(ps, pos++, questao.getTexto());
            setString(ps, pos++, questao.getComentario(), true);
            setInt(ps, pos++, questao.getSituacao().getCodigo());
            setInt(ps, pos++, questao.getComplemento().getCodigo(), true);
            setInt(ps, pos++, questao.getConcurso().getCodigo());
            setInt(ps, pos++, questao.getMateria().getCodigo());
            setInt(ps, pos++, questao.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(Questao questao, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM QUESTAO    ");
        sbSql.append(" WHERE CD_CONCURSO = ? ");
        sbSql.append("   AND CD_MATERIA  = ? ");
        sbSql.append("   AND CD_QUESTAO  = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, questao.getConcurso().getCodigo());
            setInt(ps, pos++, questao.getMateria().getCodigo());
            setInt(ps, pos++, questao.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(Connection conn, Questao questao) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_QUESTAO) AS CD_MAX ");
            sbSql.append("  FROM QUESTAO        ");
            sbSql.append(" WHERE CD_CONCURSO = ? ");
            sbSql.append("   AND CD_MATERIA  = ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, questao.getConcurso().getCodigo());
            setInt(ps, 2, questao.getMateria().getCodigo());
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return max + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static Questao fromResultSet(ResultSet rs) throws SQLException {

        // CONCURSO
        Questao questao = new Questao();
        questao.getConcurso().setCodigo(getInt(rs, "CD_CONCURSO"));
        questao.getMateria().setCodigo(getInt(rs, "CD_MATERIA"));
        questao.setCodigo(getInt(rs, "CD_QUESTAO"));
        questao.setTexto(getString(rs, "TX_QUESTAO"));
        questao.setComentario(getString(rs, "TX_COMENTARIO"));
        questao.setSituacao(Situacao.getSituacaoById(getByte(rs, "ID_SITUACAO")));
        //
        int codigoComplemento = getInt(rs, "CD_COMPLEMENTO");
        if (codigoComplemento > 0) {
            QuestaoComplemento complemento = new QuestaoComplemento();
            complemento.setCodigo(getInt(rs, "CD_COMPLEMENTO"));
            complemento.setConcursoMateria(questao.getConcursoMateria());
            complemento.setTipo(QuestaoComplementoTipo.getTipoById(getByte(rs, "ID_TIPO_COMPLEMENTO")));
            complemento.setTexto(getString(rs, "TX_TEXTO_COMPLEMENTO"));
            questao.setComplemento(complemento);
        }
        //
        return questao;
    }

}
